package org.csu.mybigpro.persistence;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;
import org.csu.mybigpro.domain.Score;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@Mapper
public interface ScoreMapper extends BaseMapper<Score> {

    @Insert("INSERT INTO score (student_id, course_id, exam_id, total_score, grade) " +
            "VALUES (#{studentId}, #{courseId}, #{examId}, #{totalScore}, #{grade})")
    int insertScore(Score score);

    @Select("SELECT * FROM score WHERE id = #{id}")
    Score getScoreById(Long id);

    @Select("SELECT * FROM score")
    List<Score> getAllScores();

    @Update("UPDATE score SET student_id=#{studentId}, course_id=#{courseId}, exam_id=#{examId}, " +
            "total_score=#{totalScore}, grade=#{grade} WHERE id=#{id}")
    int updateScore(Score score);

    @Delete("DELETE FROM score WHERE id = #{id}")
    int deleteScoreById(Long id);

    //查询某课程的平均分
    @Select("SELECT AVG(total_score) FROM score WHERE course_id = #{courseId}")
    BigDecimal getAverageScoreByCourseId(@Param("courseId") Long courseId);

    //按成绩分段统计
    @Select("""
    SELECT
        SUM(CASE WHEN total_score >= 90 THEN 1 ELSE 0 END) AS excellent,
        SUM(CASE WHEN total_score >= 80 AND total_score < 90 THEN 1 ELSE 0 END) AS good,
        SUM(CASE WHEN total_score >= 60 AND total_score < 80 THEN 1 ELSE 0 END) AS pass,
        SUM(CASE WHEN total_score < 60 THEN 1 ELSE 0 END) AS fail
    FROM score
    WHERE course_id = #{courseId}
""")
    Map<String, BigDecimal> getScoreDistribution(@Param("courseId") Long courseId);


}
